Exporting CSV | Weird Behaviour?

You don't need that temporary file. How about this.

Up vote 0 down vote favorite share g+ share fb share tw.

So, I think that I have asked this question and others related to it for more than twice. But I cannot figure out what's happening. I'm using this PHP function to export some data from a table to a CSV table.

I am testing on local host ( XAAMP ): // Export CSV By User Level public function CSVData($path, $level) { switch ($level) { case 0: $filename = $path. "/standard_members_". Date('Y').

"_". Date('m'). "_".

Date('d'). ". Csv"; break; case 1: $filename = $path.

"/special_members_". Date('Y'). "_".

Date('m'). "_". Date('d').

". Csv"; break; case 2: $filename = $path. "/admin_members_".

Date('Y'). "_". Date('m').

"_". Date('d'). ".

Csv"; break; default: break; } $sql = "SELECT user_name, user_username, user_email, user_register_date INTO OUTFILE '$filename' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM users WHERE user_level = '$level'"; if ($stmt = $this->connect->prepare($sql)) { if(!($stmt->execute())){ print_r($stmt->error); } $stmt->close(); } else { $error = true; $message'error' = true; $message'message' = CANNOT_PREPARE_DATABASE_CONNECTION_MESSAGE; return json_encode($message); } if(!is_file($filename)) { die('file not found'); } else { header('Content-Type: application/csv'); header("Content-Disposition: attachment; filename=$filename"); header('Expires: 0'); header('Pragma: no-cache'); readfile($filename); } } And I have a form that performs the action, I mean it submits the action to the PHP file which processes the above function, and this is the action code: CSVData('exports', 0); } elseif ($_GET'action' == 1) { $extension->CSVData('exports', 1); } elseif ($_GET'action' == 2) { $extension->CSVData('exports', 2); } exit();? > What happens is the following: -when I click the submit button on the form I have it sends either the value 0, 1 or 2 to the action code; -the action code gets the value and processes accordingly; -but if I haven't created the folder 'exports' on localhost if would have given me this error: Can't create/write to file 'C:xampphtdocsloginadminexports\standard_members_2012_01_28. Csv' (Errcode: 2); -and I have created it so it can work; -now that the folder it's there the CSV file is created and the data is dumped into the CSV table; -but the file that opens in my browser ( the attachment that downloads like you'd click on a file from some website and downloaded it ) it's empty even though the file exported in 'exports' has the data in it; -and another thing is that when the file already exists in the folder 'exports' the table that is created is tells me: File 'exports/admin_members_2012_01_29.

Csv' already exists So my question is why is what I described happening? And is there a way to make the MySql query overwrite the previous CSV file? Php mysql csv export link|improve this question asked Jan 29 at 9:08Roland3208 100% accept rate.

– Emil Vikström Jan 29 at 9:19 @EmilVikstrom - I don't know what to do to track down the bug. The code you see is what I tested, it exports the file as described but the file downloaded by the browser is empty. I output it in a folder on my hard drive, a separate partition, not the one my OS is, but any other file behaves as it suppose to ( if I download something from a website ).

I'm not sure, but no, I'm reading the file that is downloaded, but it shouldn't be empty right? How do I check for the file existence? I tried if(!is_file($filename)){ unlink($filename); } but still the file remains there.

– Roland Jan 29 at 9:40 A wild guess: maybe MySQL INTO OUTFILE is asynchronous operation. By the time $stmt->execute() returns and you do readfile MySQL hasn't really finished the export yet? – Sim Jan 29 at 10:01 Maybe, that could be a reason, but how to fix it?

– Roland Jan 29 at 10:09.

You don't need that temporary file. How about this: define('CSV_SEPARATOR', ','); // CSV download headers header('Content-Type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename="my. Csv"'); // A file handle to PHP output stream $fp = fopen('php://output', 'w'); // UTF-8 BOM echo "\xEF\xBB\xBF"; // Get data from database $data = ...; // List of columns $columns = array( 'User name', 'First name', 'Last name' ); foreach ($data as $key => $row) { // Write columns if ($key == 0) { fputcsv($fp, $columns, CSV_SEPARATOR); } // Write data fputcsv($fp, $row, CSV_SEPARATOR); } fclose($fp); Beautiful, isn't it?

Yes, I tried this kind of method with no success, and why not use MySql query when it's much easier to do it with a query rather than using PHP. – Roland Jan 29 at 9:42 I am not sure what you mean, the code works perfectly. And to answer to that why: a) no temp files are created, nothing is left behind, b) code is portable (no MySQL dependency) – Sim Jan 29 at 9:48 And what would that $data be?

An array or what? – Roland Jan 29 at 9:52 Yes, an array. See fputcsv documentation.

– Sim Jan 29 at 9:54 I'm using this method and it does work fine actually :) I had to replace the headers as application/csv, but it works now. My question is how would I input in the csv the fields name, per say: For the user name, on the top of all the column rows to have "Username" and so on? – Roland Jan 29 at 10:17.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.